const mysql=require('mysql');

const db=mysql.createConnection({
        host:'localhost',
        user:'root',
        password:'root',
        database:'tiantianpeiwan'
});

let Omodule={
    pw:function(id,type,fn){
        let sql=`SELECT U_photo,PU_price,U_falsename,U_qq,U_tel,U_wechat,G_names FROM  (SELECT G_names,PU_price,U_co FROM p_game a 
            LEFT JOIN p_playuser b ON a.G_id=b.G_id  
            WHERE U_co=(SELECT U_co FROM p_user WHERE U_id=${id}) AND a.G_id=${type})
             d  LEFT JOIN p_user e ON d.U_co=e.U_co WHERE U_id=${id}`;
        db.query(sql,function(err,data){
            fn(err,data)
            console.log(data)
        })
       
    },
   ku:function(kuid,fn){
    let sql1=`SELECT (h.v-z.money) AS p FROM (SELECT (a-b) AS v,e.U_co FROM (SELECT SUM(RE_money) AS a,U_co  FROM p_recharge WHERE U_co=(SELECT U_co FROM p_user WHERE U_id=${kuid})) AS e LEFT JOIN (SELECT SUM(JD_money*JD_hours) AS b,U_co  FROM p_jdtable WHERE U_co=(SELECT U_co FROM p_user WHERE U_id=111111)) AS f ON e.U_co=f.U_co) AS h LEFT JOIN (SELECT U_co,money 
        FROM (SELECT U_id,SUM(GF_num*GA_price) AS money
        FROM (SELECT U_co,GF_gagift,GF_num  
        FROM p_gift
        WHERE U_co1=(SELECT U_co
        FROM p_user
        WHERE U_id=${kuid})) AS a LEFT JOIN  p_user AS b 
        ON a.U_co=b.U_co LEFT JOIN p_giftattribute AS c ON a.GF_gagift=c.GA_id 
        GROUP BY U_names
        ORDER BY money DESC) AS q LEFT JOIN p_user AS w ON q.U_id=w.U_id) AS z ON z.U_co=h.U_co`;
        db.query(sql1,function(err,data){
            fn(err,data)
            console.log(data)
        })
   },
    go:function(id,date,stata,money,fn){
        let sql=`INSERT INTO p_jdtable(U_co,JD_puon,JD_zfdatetimes,JD_money)  VALUES (${id},"${stata}","${date}",${money})`;
        db.query(sql,function(err,data){
            fn(err,data)
            console.log(data)
        })
    },
    co:function(id,id1,date,stata,money,num,fn){
        console.log(id,id1,date,stata,money,num)
        let sql=`INSERT INTO p_jdtable (U_co,U_co1,JD_puon,JD_datetimes,JD_money,JD_hours)  VALUES ((SELECT U_co FROM p_user WHERE U_id=${id}),(SELECT U_co FROM p_user WHERE U_id=${id1}),"${stata}","${date}",${money},${num})`;
        db.query(sql,function(err,data){
            fn(err,data)
        })
    }
}


module.exports=Omodule;